#!/bin/bash

# TGPay IoT充电桩系统 - MySQL数据库管理脚本
# 使用方法: ./manage.sh [command] [options]

# 配置变量
DB_NAME="tgpay_iot"
DB_USER="root"
DB_HOST="localhost"
DB_PORT="3306"
BACKUP_DIR="./backups"
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# 日志函数
log_info() {
    echo -e "${BLUE}[INFO]${NC} $1"
}

log_success() {
    echo -e "${GREEN}[SUCCESS]${NC} $1"
}

log_warning() {
    echo -e "${YELLOW}[WARNING]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

# 检查MySQL是否安装
check_mysql() {
    if ! command -v mysql &> /dev/null; then
        log_error "MySQL客户端未安装，请先安装MySQL"
        exit 1
    fi
    
    if ! command -v mysqldump &> /dev/null; then
        log_error "mysqldump未安装，请先安装MySQL"
        exit 1
    fi
}

# 检查MySQL连接
check_connection() {
    log_info "检查MySQL连接..."
    if mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p -e "SELECT 1;" &> /dev/null; then
        log_success "MySQL连接正常"
        return 0
    else
        log_error "无法连接到MySQL服务器"
        return 1
    fi
}

# 创建数据库
create_database() {
    log_info "创建数据库 $DB_NAME..."
    
    if mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p -e "CREATE DATABASE IF NOT EXISTS \`$DB_NAME\` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;"; then
        log_success "数据库 $DB_NAME 创建成功"
    else
        log_error "数据库创建失败"
        exit 1
    fi
}

# 初始化数据库
init_database() {
    log_info "初始化数据库..."
    
    if [ ! -f "$SCRIPT_DIR/init.sql" ]; then
        log_error "初始化脚本 init.sql 不存在"
        exit 1
    fi
    
    if mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p < "$SCRIPT_DIR/init.sql"; then
        log_success "数据库初始化完成"
    else
        log_error "数据库初始化失败"
        exit 1
    fi
}

# 备份数据库
backup_database() {
    local backup_file="$1"
    
    if [ -z "$backup_file" ]; then
        backup_file="$BACKUP_DIR/${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql"
    fi
    
    # 创建备份目录
    mkdir -p "$(dirname "$backup_file")"
    
    log_info "备份数据库到 $backup_file..."
    
    if mysqldump -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --default-character-set=utf8mb4 \
        "$DB_NAME" > "$backup_file"; then
        log_success "数据库备份完成: $backup_file"
    else
        log_error "数据库备份失败"
        exit 1
    fi
}

# 恢复数据库
restore_database() {
    local backup_file="$1"
    
    if [ -z "$backup_file" ]; then
        log_error "请指定备份文件路径"
        exit 1
    fi
    
    if [ ! -f "$backup_file" ]; then
        log_error "备份文件不存在: $backup_file"
        exit 1
    fi
    
    log_warning "即将恢复数据库，这将覆盖现有数据！"
    read -p "确认继续？(y/N): " -n 1 -r
    echo
    
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        log_info "操作已取消"
        exit 0
    fi
    
    log_info "从 $backup_file 恢复数据库..."
    
    if mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p "$DB_NAME" < "$backup_file"; then
        log_success "数据库恢复完成"
    else
        log_error "数据库恢复失败"
        exit 1
    fi
}

# 删除数据库
drop_database() {
    log_warning "即将删除数据库 $DB_NAME！"
    read -p "确认删除？(y/N): " -n 1 -r
    echo
    
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        log_info "操作已取消"
        exit 0
    fi
    
    log_info "删除数据库 $DB_NAME..."
    
    if mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p -e "DROP DATABASE IF EXISTS \`$DB_NAME\`;"; then
        log_success "数据库 $DB_NAME 删除成功"
    else
        log_error "数据库删除失败"
        exit 1
    fi
}

# 显示数据库状态
show_status() {
    log_info "数据库状态信息:"
    
    echo "数据库名称: $DB_NAME"
    echo "主机地址: $DB_HOST:$DB_PORT"
    echo "用户名: $DB_USER"
    echo
    
    # 检查数据库是否存在
    if mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p -e "USE \`$DB_NAME\`;" &> /dev/null; then
        log_success "数据库 $DB_NAME 存在"
        
        # 显示表信息
        echo
        log_info "表信息:"
        mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p -e "USE \`$DB_NAME\`; SHOW TABLES;" 2>/dev/null
        
        # 显示数据统计
        echo
        log_info "数据统计:"
        mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p -e "
            USE \`$DB_NAME\`;
            SELECT 'devices' as table_name, COUNT(*) as count FROM devices
            UNION ALL
            SELECT 'charging_orders' as table_name, COUNT(*) as count FROM charging_orders;
        " 2>/dev/null
    else
        log_warning "数据库 $DB_NAME 不存在"
    fi
}

# 显示帮助信息
show_help() {
    echo "TGPay IoT充电桩系统 - MySQL数据库管理脚本"
    echo
    echo "使用方法:"
    echo "  $0 [command] [options]"
    echo
    echo "命令:"
    echo "  init                    初始化数据库（创建数据库和表）"
    echo "  create                  仅创建数据库"
    echo "  backup [file]           备份数据库到指定文件"
    echo "  restore <file>          从备份文件恢复数据库"
    echo "  drop                    删除数据库"
    echo "  status                  显示数据库状态"
    echo "  check                   检查MySQL连接"
    echo "  help                    显示此帮助信息"
    echo
    echo "示例:"
    echo "  $0 init                 # 初始化数据库"
    echo "  $0 backup               # 备份到默认位置"
    echo "  $0 backup /tmp/db.sql   # 备份到指定文件"
    echo "  $0 restore /tmp/db.sql  # 从备份恢复"
    echo "  $0 status               # 查看状态"
    echo
    echo "环境变量:"
    echo "  DB_NAME     数据库名称 (默认: $DB_NAME)"
    echo "  DB_USER     数据库用户 (默认: $DB_USER)"
    echo "  DB_HOST     数据库主机 (默认: $DB_HOST)"
    echo "  DB_PORT     数据库端口 (默认: $DB_PORT)"
}

# 主函数
main() {
    local command="$1"
    local option="$2"
    
    # 检查MySQL
    check_mysql
    
    case "$command" in
        "init")
            check_connection
            create_database
            init_database
            ;;
        "create")
            check_connection
            create_database
            ;;
        "backup")
            check_connection
            backup_database "$option"
            ;;
        "restore")
            check_connection
            restore_database "$option"
            ;;
        "drop")
            check_connection
            drop_database
            ;;
        "status")
            check_connection
            show_status
            ;;
        "check")
            check_connection
            ;;
        "help"|"--help"|"-h"|"")
            show_help
            ;;
        *)
            log_error "未知命令: $command"
            echo
            show_help
            exit 1
            ;;
    esac
}

# 执行主函数
main "$@"